package com.ejia.server;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import com.ejia.dao.BaseDao;
import com.ejia.pojo.fytype;
import com.ejia.pojo.house;
import com.ejia.pojo.money;
import com.ejia.pojo.notice;
import com.ejia.pojo.user;
import com.ejia.ui.login;
import com.mysql.cj.log.NullLogger;

public class UserService {
	
	public static int count() {
		String sql="select count(*) from tb_user";
		BaseDao baseDao=new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		try {
			while (rSet.next()) {
				return rSet.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return -1;
	}
	
	public static ArrayList<user> YZZHXXfind() {
		String sql="SELECT\n" + 
				"	CONCAT(h1.hName,h.hName) AS 门牌号,\n" + 
				"	u.RealName AS RealName\n" + 
				"FROM\n" + 
				"	ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_house h1\n" + 
				"	JOIN ejiadb.tb_user u \n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			h.parentId = h1.hId \n" + 
				"		) \n" + 
				"	AND \n" + 
				"	( h.hId = u.hid ))";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		ArrayList<user> users = new ArrayList<>();
		user user=null;
		house house=null;
		try {
			while (rSet.next()) {
				user=new user();
				house = new house();
				house.setHname(rSet.getString("门牌号"));
				user.setRealname(rSet.getString("Realname"));
				user.setHid(house);
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	
	public static ArrayList<user> YZZHXX(String name) {
		String sql="SELECT\n" + 
				"	CONCAT(h1.hName,h.hName) AS 门牌号,\n" + 
				"	u.RealName AS RealName\n" + 
				"FROM\n" + 
				"	ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_house h1\n" + 
				"	JOIN ejiadb.tb_user u \n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			h.parentId = h1.hId \n" + 
				"		) \n" + 
				"	AND \n" + 
				"	( h.hId = u.hid )	"
				+ "AND h1.hName=?)";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql,name);
		ArrayList<user> users = new ArrayList<>();
		user user=null;
		house house=null;
		try {
			while (rSet.next()) {
				user=new user();
				house = new house();
				house.setHname(rSet.getString("门牌号"));
				user.setRealname(rSet.getString("Realname"));
				user.setHid(house);
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	
	
	public static ArrayList<user> find(String Realname) {
		String sql="select * from tb_user where Realname=?";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql,Realname);
		ArrayList<user> users = new ArrayList<>();
		user user=null;
		house house=null;
		try {
			while (rSet.next()) {
				user=new user();
				user.setId(rSet.getInt("Id"));
				user.setUid(rSet.getString("Uid"));
				user.setRealname(rSet.getString("RealName"));
				user.setSex(rSet.getString("sex"));
				user.setPhone(rSet.getString("phone"));
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	
	
	public static ArrayList<user> findRealname(String uid) {
		String sql="select * from tb_user where uid=?";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql,uid);
		ArrayList<user> users = new ArrayList<>();
		user user=null;
		house house=null;
		try {
			while (rSet.next()) {
				user=new user();
				user.setId(rSet.getInt("Id"));
				user.setUid(rSet.getString("Uid"));
				user.setRealname(rSet.getString("RealName"));
				user.setSex(rSet.getString("sex"));
				user.setPhone(rSet.getString("phone"));
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	
	
	
	
	
	
	public static ArrayList<notice> findNotice() {
		String sql="select * from tb_notice";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		ArrayList<notice> notices = new ArrayList<>();
		notice notice=null;
		try {
			while (rSet.next()) {
				notice= new notice();
				notice.setNid(rSet.getInt("nid"));
				notice.setTitle(rSet.getString("title"));
				notice.setNoticetype(rSet.getString("noticetype"));
				notice.setContent(rSet.getString("content"));
				notice.setAddtime(rSet.getDate("addtime"));
				notices.add(notice);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return notices;
	}
	
	public static boolean addNotice(String title,String noticetype,String content) {
		String sql="insert into tb_notice(title,noticetype,content,addtime) values (?,?,?,CURDATE())";
		return new BaseDao().execute(sql, title,noticetype,content);
	}
	
	public static ArrayList<user> findYZXXGL() {
		String sql="SELECT\n" + 
				"	Id,\n" + 
				"	Uid,\n" + 
				"	RealName,\n" + 
				"	sex,\n" + 
				"	phone,\n" +
				"	u.hid,\n" +
				"	CONCAT( h1.hName, h.hName ) as hname \n" + 
				"FROM\n" + 
				"	ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_house h1\n" + 
				"	JOIN ejiadb.tb_user u\n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			h.parentId = h1.hId \n" + 
				"		) \n" + 
				"	AND ( h.hId = u.hid ))";
		BaseDao baseDao=new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		ArrayList<user> users= new ArrayList<>();
		user user=null;
		house house=null;
		try {
			while(rSet.next()) {
				user=new user();
				house = new house();
				user.setId(rSet.getInt("Id"));
				user.setUid(rSet.getString("Uid"));
				user.setRealname(rSet.getString("RealName"));
				user.setSex(rSet.getString("sex"));
				user.setPhone(rSet.getString("phone"));
				house.setHname(rSet.getString("hname"));
				house.setHid(rSet.getInt("hid"));
				user.setHid(house);
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	public static ArrayList<user> findByYZXXGL(String name) {
		String sql="SELECT\n" + 
				"	Id,\n" + 
				"	Uid,\n" + 
				"	RealName,\n" + 
				"	sex,\n" + 
				"	phone,\n" + 
				"	CONCAT( h1.hName, h.hName ) as hname \n" + 
				"FROM\n" + 
				"	ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_house h1\n" + 
				"	JOIN ejiadb.tb_user u\n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			h.parentId = h1.hId \n" + 
				"		) \n" + 
				"	AND ( h.hId = u.hid )"
				+ "and (RealName like ?))";
		BaseDao baseDao=new BaseDao();
		ResultSet rSet=baseDao.query(sql,"%"+name+"%");
		ArrayList<user> users= new ArrayList<>();
		user user=null;
		house house=null;
		try {
			while(rSet.next()) {
				user=new user();
				house = new house();
				user.setId(rSet.getInt("Id"));
				user.setUid(rSet.getString("Uid"));
				user.setRealname(rSet.getString("RealName"));
				user.setSex(rSet.getString("sex"));
				user.setPhone(rSet.getString("phone"));
				house.setHname(rSet.getString("hname"));
				user.setHid(house);
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	
	public static ArrayList<user> findByYZXXGL1(String name) {
		String sql="SELECT\n" + 
				"	Id,\n" + 
				"	Uid,\n" + 
				"	RealName,\n" + 
				"	sex,\n" + 
				"	phone,\n" + 
				"	CONCAT( h1.hName, h.hName ) as hname \n" + 
				"FROM\n" + 
				"	ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_house h1\n" + 
				"	JOIN ejiadb.tb_user u\n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			h.parentId = h1.hId \n" + 
				"		) \n" + 
				"	AND ( h.hId = u.hid )"
				+ "and (RealName = ?))";
		BaseDao baseDao=new BaseDao();
		ResultSet rSet=baseDao.query(sql,name);
		ArrayList<user> users= new ArrayList<>();
		user user=null;
		house house=null;
		try {
			while(rSet.next()) {
				user=new user();
				house = new house();
				user.setId(rSet.getInt("Id"));
				user.setUid(rSet.getString("Uid"));
				user.setRealname(rSet.getString("RealName"));
				user.setSex(rSet.getString("sex"));
				user.setPhone(rSet.getString("phone"));
				house.setHname(rSet.getString("hname"));
				user.setHid(house);
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	
	
	
	public static ArrayList<user> findYZXXGLBJ() {
		String sql="SELECT\n" + 
				"    Id,\n" + 
				"    Uid,\n" + 
				"    RealName,\n" + 
				"    sex,\n" + 
				"    phone,\n" + 
				"    h.hName,\n" + 
				"		h1.hName as xq,\n" + 
				"		Pwd\n" + 
				"FROM\n" + 
				"    ejiadb.tb_house h\n" + 
				"    JOIN ejiadb.tb_house h1\n" + 
				"    JOIN ejiadb.tb_user u\n" + 
				"WHERE\n" + 
				"    (\n" + 
				"        h.parentId = h1.hId \n" + 
				"    )\n" + 
				"    AND (h.hId = u.hid)";
		BaseDao baseDao=new BaseDao();
		ResultSet rSet=baseDao.query(sql);
		ArrayList<user> users= new ArrayList<>();
		user user=null;
		house house=null;
		try {
			while(rSet.next()) {
				user=new user();
				house = new house();
				user.setId(rSet.getInt("Id"));
				user.setUid(rSet.getString("Uid"));
				user.setRealname(rSet.getString("RealName"));
				user.setSex(rSet.getString("sex"));
				user.setPhone(rSet.getString("phone"));
				user.setPwd(rSet.getString("pwd"));
				house.setHname(rSet.getString("xq"));
				house.setRemark(rSet.getString("hname"));
				user.setHid(house);
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	
	
	
	
	
	
	
	public static boolean update(int id,String pwd) {
		String sql="update tb_user set pwd=? where id=?";
		return new BaseDao().execute(sql,pwd,id);
	}
	
	public static boolean update2(String uid,String pwd1,String pwd2) {
		String sql="update tb_user set pwd=? where uid=? and pwd=?";
		return new BaseDao().execute(sql,pwd2,uid,pwd1);
	}
	
	
	
	
	
	public static boolean update1(String uid,String pwd,String realname,String sex,String phone,int hid,String birth,String email,String remark,int id) {
		String sql="update tb_user set uid=?,pwd=?,realname=?,sex=?,phone=?,hid=?,birthday=?,email=?,remark=? where id=?";
		return new BaseDao().execute(sql,uid,pwd,realname,sex,phone,hid,birth,email,remark,id);
	}
	
	
	
	public static boolean delete(int id) {
		String sql="delete from tb_user where id=?";
		return new BaseDao().execute(sql,id);
	}
	
	public static boolean yzxxgltj(String uid,String pwd,String realname,String sex,String phone,int hid) {
		String sql="insert into tb_user(uid,pwd,realname,sex,phone,hid) values(?,?,?,?,?,?)";
		return new BaseDao().execute(sql,uid,pwd,realname,sex,phone,hid);
	}
	
	public static boolean yzxxglxg(String uid,String pwd,String realname,String sex,String phone,int hid) {
		String sql="update tb_user set pwd=?,realname=?,sex=?,phone=?,hid=? where uid=?";
		return new BaseDao().execute(sql,pwd,realname,sex,phone,hid,uid);
	}
	
	
	public static ArrayList<user> find1(String realname) {
		String sql="SELECT\n" + 
				"	u.id,\n" + 
				"	h.address,\n" + 
				"	h.parentId\n" + 
				"FROM\n" + 
				"	ejiadb.tb_house h\n" + 
				"	JOIN ejiadb.tb_house h1\n" + 
				"	JOIN ejiadb.tb_user u \n" + 
				"WHERE\n" + 
				"	((\n" + 
				"			h.parentId = h1.hId \n" + 
				"		) \n" + 
				"	AND \n" + 
				"	( h.hId = u.hid )\n" + 
				"	AND u.RealName=?)";
		BaseDao baseDao= new BaseDao();
		ResultSet rSet=baseDao.query(sql, realname);
		ArrayList<user> users = new ArrayList<>();
		user user= null;
		house house= null;
		try {
			while(rSet.next()) {
				user = new user();
				house = new house();
				house.setAddress(rSet.getString("address"));
				house.setParentid(rSet.getInt("parentId"));
				user.setId(rSet.getInt("id"));
				user.setHid(house);
				users.add(user);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return users;
	}
	
	
	public static boolean add(int id,int parentid,int status,String city,String time) {
		String sql="insert into tb_fangkong(userid,houseid,status,city,updatetime) values(?,?,?,?,?)";
		return new BaseDao().execute(sql, id,parentid,status,city,time);
	}
	
	public static boolean update3(int userid,int parentid,int status,String city,String time,int id) {
		String sql="update tb_fangkong set userid=?,houseid=?,status=?,city=?,updatetime=? where fkid=?";
		return new BaseDao().execute(sql,userid,parentid,status,city,time,id);
	}
	
	
	
	
	public static boolean deletecar(int id) {
		String sql="delete from tb_car where userid=?";
		return new BaseDao().execute(sql,id);
	}
	
	public static boolean deletemoney(int mid) {
		String sql="delete from tb_money where userid=?";
		return new BaseDao().execute(sql, mid);
	}
	
	public static boolean deletehouse(int hid) {
		String sql="delete from tb_house where hid=?";
		return new BaseDao().execute(sql, hid);
	}
	
}
